<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>质量管理系统核心数据库设计</title>
    <script src="https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
            background-color: #f5f5f5;
        }
        .container {
            background-color: white;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            max-width: 1800px;
            margin: 0 auto;
        }
        h1 {
            color: #333;
            text-align: center;
            margin-bottom: 30px;
        }
        .mermaid {
            display: flex;
            justify-content: center;
            overflow-x: auto;
        }
        .description {
            margin-top: 30px;
            padding: 15px;
            background-color: #f9f9f9;
            border-left: 4px solid #4b89dc;
            border-radius: 4px;
        }
        .description h2 {
            margin-top: 0;
            color: #4b89dc;
        }
        .module {
            margin-bottom: 40px;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>质量管理系统核心数据库设计</h1>
        
        <div class="module">
            <h2>产品与批次管理</h2>
            <div class="mermaid">
erDiagram
    product {
        int id PK "产品ID"
        varchar code "产品编号"
        varchar name "产品名称"
        int category_id FK "产品分类ID"
        varchar specification "产品规格"
        varchar material "产品材质"
        varchar unit "计量单位"
        varchar grade "产品等级"
        tinyint status "状态"
    }
    
    product_category {
        int id PK "分类ID"
        varchar name "分类名称"
        int parent_id "父级分类ID"
        int order_num "显示顺序"
    }
    
    batch {
        int id PK "批次ID"
        varchar batch_no "批次号"
        int product_id FK "产品ID"
        date production_date "生产日期"
        varchar production_line "生产线"
        int quantity "批次数量"
        tinyint status "状态"
        varchar product_name "产品名称"
    }
    
    product ||--o{ batch : "生产"
    product }|--|| product_category : "属于"
            </div>
        </div>

        <div class="module">
            <h2>工序与生产过程</h2>
            <div class="mermaid">
erDiagram
    process {
        int id PK "工序ID"
        varchar code "工序编号"
        varchar name "工序名称"
        int type_id FK "工序类型ID"
        varchar description "工序描述"
        decimal standard_time "标准工时"
        tinyint status "状态"
        varchar type_name "工序类型名称"
    }
    
    process_type {
        int id PK "类型ID"
        varchar name "类型名称"
        int parent_id "父级类型ID"
        int order_num "显示顺序"
    }
    
    process_record {
        int id PK "记录ID"
        int batch_id FK "批次ID"
        int process_id FK "工序ID"
        varchar operator "操作人员"
        datetime operation_date "操作时间"
        varchar equipment "使用设备"
        varchar process_result "工序结果"
        varchar process_name "工序名称"
        int equipment_id "设备ID"
        varchar status "状态"
    }
    
    process_param {
        bigint id PK "参数ID"
        bigint process_id FK "工序ID"
        varchar param_name "参数名称"
        varchar code "参数编码"
        varchar type "参数类型"
        varchar unit "参数单位"
        varchar standard_value "标准值"
        varchar upper_tolerance "公差上限"
        varchar lower_tolerance "公差下限"
    }
    
    product_process {
        int id PK "关联ID"
        int product_id FK "产品ID"
        int process_id FK "工序ID"
        int sequence "排序"
        tinyint is_required "是否必须"
    }
    
    process }|--|| process_type : "类型"
    process ||--o{ process_record : "执行"
    process ||--o{ process_param : "包含"
    process ||--o{ product_process : "关联"
            </div>
        </div>

        <div class="module">
            <h2>质量检验与缺陷管理</h2>
            <div class="mermaid">
erDiagram
    inspection_task {
        int id PK "任务ID"
        varchar task_code "检验任务编号"
        int product_id FK "产品ID"
        int batch_id FK "批次ID"
        int process_id FK "工序ID"
        varchar inspection_type "检验类型"
        date plan_date "计划检验日期"
        varchar inspector "检验人员"
        int sample_quantity "抽样数量"
        varchar inspection_result "检验结论"
        int task_status "任务状态"
    }
    
    inspection_data {
        int id PK "数据ID"
        int task_id FK "检验任务ID"
        int param_id FK "参数ID"
        varchar param_name "参数名称"
        varchar standard_value "标准值"
        varchar measured_value "实测值"
        tinyint is_qualified "是否合格"
        int defect_id FK "缺陷ID"
        varchar result "结果"
    }
    
    defect {
        int id PK "缺陷ID"
        varchar code "缺陷代码"
        varchar name "缺陷名称"
        varchar defect_type "缺陷类型"
        varchar defect_level "缺陷等级"
        varchar description "缺陷描述"
        tinyint status "状态"
        int parent_id "父级ID"
    }
    
    defect_record {
        int id PK "记录ID"
        int inspection_task_id FK "检验任务ID"
        int defect_id FK "缺陷ID"
        varchar location "缺陷位置"
        varchar severity "严重程度"
        varchar image_url "图片URL"
        varchar repair_method "修复方法"
        varchar repair_result "修复结果"
        varchar defect_name "缺陷名称"
        varchar status "状态"
    }
    
    nonconforming {
        int id PK "不合格品ID"
        int task_id FK "检验任务ID"
        int product_id FK "产品ID"
        int batch_id FK "批次ID"
        int quantity "不合格数量"
        int defect_id FK "缺陷ID"
        varchar defect_description "缺陷描述"
        varchar disposition "处理方式"
        varchar handler "处理人"
        datetime handle_time "处理时间"
        varchar process_status "处理状态"
    }
    
    repair_order {
        int id PK "工单ID"
        varchar order_no "工单编号"
        int defect_record_id FK "缺陷记录ID"
        varchar repair_type "维修类型"
        varchar repair_level "维修级别"
        varchar assign_to "分配给"
        varchar planned_date "计划日期"
        varchar actual_date "实际日期"
        varchar repair_result "维修结果"
        varchar status "状态"
    }
    
    inspection_task ||--o{ inspection_data : "产生"
    inspection_task ||--o{ defect_record : "记录"
    defect ||--o{ defect_record : "分类"
    defect_record ||--o{ repair_order : "处理"
    inspection_task ||--o{ nonconforming : "产生"
            </div>
        </div>

        <div class="module">
            <h2>SPC分析与质量证书</h2>
            <div class="mermaid">
erDiagram
    spc_analysis {
        int id PK "分析ID"
        int product_id FK "产品ID"
        int batch_id FK "批次ID"
        int item_id "参数ID"
        int sample_size "样本量"
        decimal avg_value "平均值"
        decimal std_dev "标准差"
        decimal min_value "最小值"
        decimal max_value "最大值"
        decimal cpk "Cpk值"
        decimal cp "Cp值"
        datetime analysis_date "分析日期"
    }
    
    measurement {
        int id PK "测量ID"
        int inspection_task_id FK "检验任务ID"
        int param_id FK "参数ID"
        decimal measured_value "测量值"
        datetime measurement_time "测量时间"
        varchar operator "操作员"
        tinyint is_in_control "是否在控制内"
        int equipment_id "测量设备ID"
        varchar param_name "参数名称"
        varchar standard_value "标准值"
        varchar result "结果"
    }
    
    certificate_template {
        bigint id PK "模板ID"
        varchar name "模板名称"
        varchar type "模板类型"
        varchar template_format "模板格式"
        longtext content "HTML内容"
        varchar status "状态"
    }
    
    certificate {
        bigint id PK "证书ID"
        varchar certificate_no "证书编号"
        bigint template_id FK "模板ID"
        int product_id FK "产品ID"
        int batch_id FK "批次ID"
        varchar type "证书类型"
        date issue_date "签发日期"
        varchar issuer "签发人"
        varchar certificate_status "状态"
    }
    
    standard {
        int id PK "标准ID"
        varchar code "标准编号"
        varchar name "标准名称"
        int category_id FK "标准分类ID"
        varchar type "标准类型"
        varchar version "标准版本"
        date publish_date "发布日期"
        tinyint status "状态"
    }
    
    standard_param {
        int id PK "参数ID"
        int standard_id FK "标准ID"
        varchar name "参数名称"
        varchar unit "参数单位"
        decimal min_value "最小值"
        decimal max_value "最大值"
        decimal nominal_value "标称值"
        varchar data_type "数据类型"
    }
    
    spc_analysis }|--|| product : "分析"
    spc_analysis }|--|| batch : "分析"
    certificate_template ||--o{ certificate : "使用"
    standard ||--o{ standard_param : "包含"
            </div>
        </div>
        
        <div class="description">
            <h2>数据库模块说明</h2>
            <p>质量管理系统数据库设计包含以下核心模块：</p>
            <ol>
                <li><strong>产品与批次管理</strong> - 管理产品信息、产品分类和生产批次</li>
                <li><strong>工序与生产过程</strong> - 定义生产工序、工序参数和记录生产过程</li>
                <li><strong>质量检验与缺陷管理</strong> - 管理检验任务、记录检验数据、缺陷分类与处理</li>
                <li><strong>SPC分析与质量证书</strong> - 统计过程控制分析、测量数据管理和质量证书生成</li>
            </ol>
            <p>数据库设计采用关系型结构，通过外键关联实现各模块间的数据一致性和完整性。</p>
        </div>
    </div>

    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            er: {
                diagramPadding: 20,
                layoutDirection: 'LR',  // 关键设置：LR表示从左到右的布局
                entityPadding: 15,
                useMaxWidth: false
            },
            flowchart: {
                useMaxWidth: false,
                htmlLabels: true
            }
        });
    </script>
</body>
</html> 